from datetime import datetime

from sqlalchemy import func, or_, and_, exc
from collections import defaultdict

from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist

db_utils = DBUtils()

class TransactionCharactersService:

    def __init__(self):
        self.db = DBUtils()

    # 根据公司名称查询当年的所有交易特征（通过时间区间条件查询），区分公司类型
    def select_trading_indicators_data(self, session, companyName, company_type, start_date, end_date):
        if (start_date, end_date) not in (None, ''):
            year = datetime.now().year
        else:
            year = start_date[:4]
        if companyName is not None:
            sy_data = None
            xy_data = None
            try:
                if company_type == 1:
                    # 获取上游交易总额和交易笔数
                    sy_data = session.query(func.count(sklist.FKJE),func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).all()
                    # 获取下游交易总额和交易笔数
                    xy_data = session.query(func.count(fklist.FKJE),func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).all()
                else:
                    # 获取上游交易总额和交易笔数
                    sy_data = session.query(func.count(fklist.FKJE),func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).all()
                    # 获取下游交易总额和交易笔数
                    xy_data = session.query(func.count(sklist.FKJE),func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            sy_total_count,sy_total_amount = sy_data[0]
            xy_total_count,xy_total_amount = xy_data[0]
            # 全年交易额
            if sy_total_amount is not None or xy_total_amount is not None:
                qn_total_amount = sy_total_amount + xy_total_amount
            else:
                sy_total_amount = 0
                xy_total_amount = 0
                qn_total_amount = 0
            sy_jy_max_data = None
            xy_jy_max_data = None
            try:
                # 获取交易最频繁的月数据
                sy_jy_max_data = session.query(func.month(sklist.SCRQ),func.count(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                ).group_by(func.month(sklist.SCRQ)).order_by(func.count(sklist.FKJE).desc()).all()
                xy_jy_max_data = session.query(func.month(fklist.SCRQ),func.count(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                ).group_by(func.month(fklist.SCRQ)).order_by(func.count(fklist.FKJE).desc()).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            merged_data = {}
            for province, count in sy_jy_max_data:
                if province in merged_data:
                    merged_data[province] += count
                else:
                    merged_data[province] = count
            for province, count in xy_jy_max_data:
                if province in merged_data:
                    merged_data[province] += count
                else:
                    merged_data[province] = count
            sxy_jy_max_data = [{province: count} for province, count in merged_data.items()]
            if len(sxy_jy_max_data) > 0:
                # 交易最频繁的月
                sxy_jy_max_month = next(iter(sxy_jy_max_data[0].keys()))
                sy_max_total_data = None
                xy_max_total_data = None
                try:
                    # 交易最频繁的月占全年交易额的百分比
                    sy_max_total_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,
                                                                                                           sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            sklist.SKZHMC == companyName,
                            ~or_(
                                sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                                sklist.FKZHMC == "<NULL>"
                            ),
                            func.month(sklist.SCRQ) == sxy_jy_max_month,
                            sklist.SCRQ >= start_date,  # 起始日期
                            sklist.SCRQ <= end_date  # 截止日期
                        )).all()
                    xy_max_total_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,
                                                                                                           fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            fklist.FKZHMC == companyName,
                            ~or_(
                                fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                                fklist.SKZHMC == "<NULL>"
                            ),
                            func.month(fklist.SCRQ) == sxy_jy_max_month,
                            fklist.SCRQ >= start_date,  # 起始日期
                            fklist.SCRQ <= end_date  # 截止日期
                        )).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        session.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
                sxy_jy_max_total_amount = sy_max_total_data[0][0] + xy_max_total_data[0][0]
                if qn_total_amount > 0:
                    max_month_jy_ratio = str(round((sxy_jy_max_total_amount / qn_total_amount) * 100, 2)) + '%'
                else:
                    max_month_jy_ratio = str(0) + '%'
            else:
                sxy_jy_max_month = '暂无'
                max_month_jy_ratio = str(0) + '%'
            sy_yc_jy_total_data = None
            xy_yc_jy_total_data = None
            try:
                # 月初：1-10号，月末20-30号
                # 月初交易总额
                sy_yc_jy_total_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.JYND == year,
                        func.day(sklist.SCRQ) <= 10,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(sklist.SCRQ)).all()
                xy_yc_jy_total_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.JYND == year,
                        func.day(fklist.SCRQ) <= 10,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(fklist.SCRQ)).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            yc_jy_total_amount = sum(item[0] for item in sy_yc_jy_total_data) + sum(item[0] for item in xy_yc_jy_total_data)
            if qn_total_amount > 0:
                yc_jy_ratio = str(round((yc_jy_total_amount / qn_total_amount) * 100, 2)) + '%'
            else:
                yc_jy_ratio = str(0) + '%'
            sy_ym_jy_total_data = None
            xy_ym_jy_total_data = None
            try:
                # 月末交易总额
                sy_ym_jy_total_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.JYND == year,
                        func.day(sklist.SCRQ) >= 20,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    )).group_by(func.month(sklist.SCRQ)).all()
                xy_ym_jy_total_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.JYND == year,
                        func.day(fklist.SCRQ) >= 20,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(fklist.SCRQ)).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            ym_jy_total_amount = sum(item[0] for item in sy_ym_jy_total_data) + sum(item[0] for item in xy_ym_jy_total_data)
            if qn_total_amount > 0:
                ym_jy_ratio = str(round((ym_jy_total_amount / qn_total_amount) * 100, 2)) + '%'
            else:
                ym_jy_ratio = str(0) + '%'
            sy_nc_jy_total_data = None
            xy_nc_jy_total_data = None
            try:
                # 年初：1-2月份，年末：11-12月份
                # 年初交易额
                sy_nc_jy_total_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.JYND == year,
                        func.month(sklist.SCRQ) <= 2,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(sklist.SCRQ)).all()
                xy_nc_jy_total_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.JYND == year,
                        func.month(fklist.SCRQ) <= 2,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(fklist.SCRQ)).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            nc_jy_total_amount = sum(item[0] for item in sy_nc_jy_total_data) + sum(item[0] for item in xy_nc_jy_total_data)
            if qn_total_amount > 0:
                nc_jy_ratio = str(round((nc_jy_total_amount / qn_total_amount) * 100, 2)) + '%'
            else:
                nc_jy_ratio = str(0) + '%'
            sy_nm_jy_total_data = None
            xy_nm_jy_total_data = None
            try:
                # 年末交易额
                sy_nm_jy_total_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        sklist.SKZHMC == companyName,
                        ~or_(
                            sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                            sklist.FKZHMC == "<NULL>"
                        ),
                        sklist.JYND == year,
                        func.month(sklist.SCRQ) >= 11,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(sklist.SCRQ)).all()
                xy_nm_jy_total_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo,fklist.SKZHMC == BusinessInfo.GSMC).filter(
                    and_(
                        fklist.FKZHMC == companyName,
                        ~or_(
                            fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                            fklist.SKZHMC == "<NULL>"
                        ),
                        fklist.JYND == year,
                        func.month(fklist.SCRQ) >= 11,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                )).group_by(func.month(fklist.SCRQ)).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            nm_jy_total_amount = sum(item[0] for item in sy_nm_jy_total_data) + sum(item[0] for item in xy_nm_jy_total_data)
            if qn_total_amount > 0:
                nm_jy_ratio = str(round((nm_jy_total_amount / qn_total_amount) * 100, 2)) + '%'
            else:
                nm_jy_ratio = str(0) + '%'
            result = {
                'yc_jy_ratio': yc_jy_ratio, # 月初占比
                'ym_jy_ratio': ym_jy_ratio, # 月末占比
                'nc_jy_ratio': nc_jy_ratio, # 年初占比
                'nm_jy_ratio': nm_jy_ratio, # 年末占比
                'sy_total_amount': round(sy_total_amount/10000,2), # 上游交易总额
                'xy_total_amount': round(xy_total_amount/10000,2), # 下游交易总额
                'sy_total_count': sy_total_count, # 上游交易笔数
                'xy_total_count': xy_total_count, # 下游交易笔数
                'sxy_jy_max_month': sxy_jy_max_month, # 交易最频繁的月
                'max_month_jy_ratio': max_month_jy_ratio # 交易最频繁的月在全年交易额中的占比
            }
        else:
            result = None
        return result

    def select_trading_statistics_data(self, session, companyName, company_type, start_date, end_date):
        if (start_date, end_date) not in (None, ''):
            year = datetime.now().year
        else :
            year = start_date[:4]
        months = [1,2,3,4,5,6,7,8,9,10,11,12]
        if companyName is not None:
            sy_jy_data = None
            xy_jy_data = None
            try:
                if company_type == 1:
                    # 每月上游交易金额
                    sy_jy_data = session.query(func.month(sklist.SCRQ),func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo,sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            sklist.SKZHMC == companyName,
                            ~or_(
                                sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                                sklist.FKZHMC == "<NULL>"
                            ),
                            sklist.JYND == year,
                            sklist.SCRQ >= start_date,  # 起始日期
                            sklist.SCRQ <= end_date  # 截止日期
                    )).group_by(func.month(sklist.SCRQ)).all()
                    # 每月下游交易金额
                    xy_jy_data = session.query(func.month(fklist.SCRQ), func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            fklist.FKZHMC == companyName,
                            ~or_(
                                fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                                fklist.SKZHMC == "<NULL>"
                            ),
                            fklist.JYND == year,
                            fklist.SCRQ >= start_date,  # 起始日期
                            fklist.SCRQ <= end_date  # 截止日期
                    )).group_by(func.month(fklist.SCRQ)).all()
                else:
                    # 每月上游交易金额
                    sy_jy_data = session.query(func.month(fklist.SCRQ), func.sum(fklist.FKJE)).select_from(fklist).outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            fklist.FKZHMC == companyName,
                            ~or_(
                                fklist.SKZHMC == "待报解预算收入（财库联网集中户）",
                                fklist.SKZHMC == "<NULL>"
                            ),
                            fklist.JYND == year,
                            fklist.SCRQ >= start_date,  # 起始日期
                            fklist.SCRQ <= end_date  # 截止日期
                    )).group_by(func.month(fklist.SCRQ)).all()
                    # 每月下游交易金额
                    xy_jy_data = session.query(func.month(sklist.SCRQ), func.sum(sklist.FKJE)).select_from(sklist).outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            sklist.SKZHMC == companyName,
                            ~or_(
                                sklist.FKZHMC == "待报解预算收入（财库联网集中户）",
                                sklist.FKZHMC == "<NULL>"
                            ),
                            sklist.JYND == year,
                            sklist.SCRQ >= start_date,  # 起始日期
                            sklist.SCRQ <= end_date  # 截止日期
                    )).group_by(func.month(sklist.SCRQ)).all()
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    session.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
            sy_jy_list = []
            for month in months:
                md = {}
                if len(sy_jy_data) > 0:
                    for mon, value in sy_jy_data:
                        if mon == month:
                            md[mon] = round(value/10000,2)
                            break
                        else:
                            md[month] = 0
                else:
                    md[month] = 0
                sy_jy_list.append(md)
            xy_jy_list = []
            for month in months:
                md = {}
                if len(xy_jy_data) > 0:
                    for mon, value in xy_jy_data:
                        if mon == month:
                            md[mon] = round(value / 10000, 2)
                            break
                        else:
                            md[month] = 0
                else:
                    md[month] = 0
                xy_jy_list.append(md)
            result = {
                'sy_jy_list': sy_jy_list, # 上游每月交易总金额
                'xy_jy_list': xy_jy_list # 下游每月交易总金额
            }
        else:
            result = None
        return result
